using System;
using System.Collections.Generic;
using System.Text;
using Decav.Security;
using Decav.Reality;
using System.Data.SqlClient;
using System.Data;
using System.Net.Mail;
using Decav.Data;
using System.IO;
using System.Threading;
using Decav.Gatsb.Security;
using System.Security;
using Decav.Gatsb.Messaging;
using Decav.Gatsb.Application;
using Decav.Geography;
namespace Decav.Gatsb.Data
{
    /// <summary>
    /// Connects to the Gatsb SQL database to retrieve and save information.
    /// </summary>
    public class GatsbSqlDataAdapter : IGatsbDataAdapter
    {
        private TType CreateUserObject<TType>(Guid userId)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetUserById", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@Id", userId);
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            return DataRecordAdapter.CreateInstance<TType>((IDataRecord)reader);
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        #region IGatsbDataAdapter Members

        public bool CheckUserExists(EmailAddress emailAddress)
        {

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.CheckUserExistsByEmail", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Email", emailAddress.ToString());
                        return (bool)cmd.ExecuteScalar();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public bool CheckUserExists(PhoneNumber telNumber)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.CheckUserExistsByPhone", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@MobileTel", telNumber.ToString());
                        return (bool)cmd.ExecuteScalar();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public User GetUser(EmailAddress emailAddress)
        {
            if (emailAddress == null)
                throw new ArgumentNullException("emailAddress");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetUserByEmail", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        cmd.Parameters.AddWithValue("@Email", emailAddress.ToString());
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            return DataRecordAdapter.CreateInstance<User>((IDataRecord)reader);
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public User GetUser(Guid userId)
        {
            return CreateUserObject<User>(userId);
        }

        public User GetUser(PhoneNumber telNumber)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetUserByPhone", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        cmd.Parameters.AddWithValue("@MobileTel", telNumber.ToString());
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            return DataRecordAdapter.CreateInstance<User>((IDataRecord)reader);
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public UserLoginResult AuthenticateUser(EmailAddress emailAddress, HashedPassword password)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.AuthenticateUser", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Email", emailAddress.ToString());
                        cmd.Parameters.AddWithValue("@PasswordHash", password.ToByteArray());
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            if ((bool)reader["Authenticated"])
                            {
                                return new UserLoginResult(true, (bool)reader["RequiresValidation"], (bool)reader["RequiresInitialSetup"], DataRecordAdapter.CreateInstance<User>((IDataRecord)reader));
                            }
                            else
                            {
                                return UserLoginResult.Failed;
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public UserLoginResult AuthenticateUser(PhoneNumber mobileTel, EmailAddress smsEmail)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.AuthenticateUserByMobileTel", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@MobileTel", mobileTel.ToString());

                        if (smsEmail != null)
                            cmd.Parameters.AddWithValue("@SmsEmailAddress", smsEmail.ToString());

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            if ((bool)reader["Authenticated"])
                            {
                                return new UserLoginResult(true, (bool)reader["RequiresValidation"],
                                    (bool)reader["RequiresInitialSetup"], DataRecordAdapter.CreateInstance<User>((IDataRecord)reader));
                            }
                            else
                            {
                                return UserLoginResult.Failed;
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public UserProfile GetUserProfile(Guid userId)
        {
            return CreateUserObject<UserProfile>(userId);
        }

        public void CreateUser(User user, HashedPassword password)
        {
            CreateUser(user, password, null);
        }

        public void CreateUser(User user, HashedPassword password, CreateUserArgs args)
        {
            if (user == null)
                throw new ArgumentNullException("user");

            if (password == HashedPassword.Empty)
                throw new ArgumentException("A password must be defined to create a pssword.");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.CreateUser", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Id", user.Id);
                        cmd.Parameters.AddWithValue("@Email", user.Email.ToString());
                        cmd.Parameters.AddWithValue("@PasswordHash", password.ToByteArray());
                        cmd.Parameters.AddWithValue("@MobileTel", user.MobilePhoneNumber.ToString());

                        cmd.Parameters.AddWithValue("@PostalCode", user.PostalCode.ToString());
                        cmd.Parameters.AddWithValue("@PhoneProvider", user.TelephoneServiceProvider.GetTypeString());
                        cmd.Parameters.AddWithValue("@Name", user.Name.ToString());

                        if (args != null)
                        {
                            cmd.Parameters.AddWithValue("@IsTempUser", args.CreateAsTemporaryUser);
                        }

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public void UpdateUserProfile(User user, UserProfile profile)
        {
            if (user == null)
                throw new ArgumentNullException("user");

            if (profile == null)
                throw new ArgumentNullException("profile");

            if (user.Id != GatsbPrincipal.Current.Identity.UserId)
                throw new SecurityException("Users can only update their own profiles.");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.UpdateUser", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@UserId", user.Id);
                        cmd.Parameters.AddWithValue("@Name", user.Name.ToString());
                        cmd.Parameters.AddWithValue("@MobileTel", user.MobilePhoneNumber.ToString());
                        cmd.Parameters.AddWithValue("@TelephoneServiceProvider", user.TelephoneServiceProvider.GetTypeString());
                        cmd.Parameters.AddWithValue("@Occupation", user.Occupation);
                        cmd.Parameters.AddWithValue("@PostalCode", user.PostalCode);
                        cmd.Parameters.AddWithValue("@City", user.City);
                        cmd.Parameters.AddWithValue("@Email", user.Email.ToString());
                        
                        if (user.State == UsState.Empty)
                            cmd.Parameters.AddWithValue("@State", DBNull.Value);
                        else
                            cmd.Parameters.AddWithValue("@State", user.State.PostalCode);
                        
                        cmd.Parameters.AddWithValue("@Gender", (int)profile.Gender);
                        cmd.Parameters.AddWithValue("@About", profile.About);
                        cmd.Parameters.AddWithValue("@ProfilePermissions", (int)user.ProfilePrivacy);
                        cmd.Parameters.AddWithValue("@LocationPermissions", (int)user.LocationPrivacy);
                        cmd.Parameters.AddWithValue("@DefaultPostPermissions", (int)user.DefaultPostPrivacy);

                        if (user.AlternateSmsAddress != EmailAddress.Empty)
                            cmd.Parameters.AddWithValue("@SmsEmailAlternate", user.AlternateSmsAddress.ToString());
                        else
                            cmd.Parameters.AddWithValue("@SmsEmailAlternate", DBNull.Value);

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public bool ChangeUserPassword(HashedPassword oldPassword, HashedPassword newPassword)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.ChangeUserPassword", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        if (oldPassword != HashedPassword.Empty)
                            cmd.Parameters.AddWithValue("@OldPassword", oldPassword.ToByteArray());
                        
                        cmd.Parameters.AddWithValue("@NewPassword", newPassword.ToByteArray());

                        return (bool)cmd.ExecuteScalar();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Gets the attachments for a <see cref="Post"/>.
        /// </summary>
        /// <param name="postId">The ID of the post to get attachments for.</param>
        /// <returns>An array of the attachments on a post.</returns>
        public PostAttachment[] GetPostAttachments(Guid postId)
        {
            List<PostAttachment> attachments = new List<PostAttachment>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.GetPostAttachments", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@PostId", postId);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                attachments.Add(DataRecordAdapter.CreateInstance<PostAttachment>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return attachments.ToArray();
        }

        public void CreatePost(Post post)
        {
            if (post == null)
                throw new ArgumentNullException("post");

            SqlTransaction txn = null;
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    txn = cxn.BeginTransaction();
                    using (SqlCommand cmd = new SqlCommand("dbo.CreatePost", cxn))
                    {
                        cmd.Transaction = txn;
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        cmd.Parameters.AddWithValue("@PostId", post.Id);
                        cmd.Parameters.AddWithValue("@CreateDate", post.CreateDate);
                        cmd.Parameters.AddWithValue("@Subject", post.Subject);

                        if (post.LocationId == Guid.Empty)
                            cmd.Parameters.AddWithValue("@Location", DBNull.Value);
                        else
                            cmd.Parameters.AddWithValue("@Location", post.LocationId);

                        cmd.Parameters.AddWithValue("@Body", post.Body);

                        cmd.ExecuteNonQuery();
                    }

                    foreach (PostAttachment attachment in post.Attachments)
                    {
                        using (SqlCommand cmd = new SqlCommand("dbo.CreatePostAttachment", cxn))
                        {
                            cmd.Transaction = txn;
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue("@AttachmentId", attachment.Id);
                            cmd.Parameters.AddWithValue("@PostId", post.Id);
                            cmd.ExecuteNonQuery();
                        }
                    }

                    txn.Commit();
                }
                catch (SqlException ex)
                {
                    txn.Rollback();
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    if (txn != null)
                        txn.Dispose();

                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Gets the locations that a user has created but not filled out all information for.
        /// </summary>
        /// <returns>The locations the users created but not filled out all information for.</returns>
        public Location[] GetUserIncompleteLocations()
        {
            List<Location> items = new List<Location>();

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetUserIncompleteLocations", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                items.Add(DataRecordAdapter.CreateInstance<Location>((IDataRecord)reader));
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return items.ToArray();
        }

        public void CreateLocation(Location location)
        {
            if (location == null)
                throw new ArgumentNullException("location");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.CreateLocation", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddLocationParameters(location, cmd);

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Updates a location that already exists in the database.
        /// </summary>
        /// <param name="location">The location to update.</param>
        public void UpdateLocation(Location location)
        {
            if (location == null)
                throw new ArgumentNullException("location");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.UpdateLocation", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddLocationParameters(location, cmd);

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Merges two locations post information together and removes the
        /// location that was merged.
        /// </summary>
        /// <param name="merge">The location that will be merged into the
        /// <paramref name="mergeInto"/> location and then be deleted.</param>
        /// <param name="mergeInto">The location that will be kept as a composite
        /// of the <paramref name="merge"/> location and itself.</param>
        public void MergeLocation(Location merge, Location mergeInto)
        {
            if (merge == null)
                throw new ArgumentNullException("merge");

            if (mergeInto == null)
                throw new ArgumentNullException("mergeInto");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.MergeLocations", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);

                        cmd.Parameters.AddWithValue("@MergeLocationId", merge.Id);
                        cmd.Parameters.AddWithValue("@IntoLocationId", mergeInto.Id);
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        private void AddLocationParameters(Location location, SqlCommand cmd)
        {
            AddRequestingUserId(cmd);
            cmd.Parameters.AddWithValue("@LocationId", location.Id);
            cmd.Parameters.AddWithValue("@Type", (int)location.Type);

            if (location.GeographicCoordinates != LatLongPoint.Empty)
                cmd.Parameters.AddWithValue("@Coordinates", location.GeographicCoordinates.ToString());

            cmd.Parameters.AddWithValue("@Name", location.Name);

            if (location.Description != null && location.Description.Length > 0)
                cmd.Parameters.AddWithValue("@Description", location.Description);
            else
                cmd.Parameters.AddWithValue("@Description", DBNull.Value);


            if (location.Address != null)
            {
                cmd.Parameters.AddWithValue("@Address1", location.Address.Address1);
                cmd.Parameters.AddWithValue("@City", location.Address.City);
                cmd.Parameters.AddWithValue("@State", location.Address.Region);
                cmd.Parameters.AddWithValue("@PostalCode", location.Address.PostalCode);
            }
            else
            {
                // HACK:  Replace this with user defaults in the future
                cmd.Parameters.AddWithValue("@City", "New York");
                cmd.Parameters.AddWithValue("@State", "NY");
                cmd.Parameters.AddWithValue("@PostalCode", GatsbApplication.Current.CurrentUser.PostalCode);
            }

            if (location.TelephoneNumber != PhoneNumber.Empty)
                cmd.Parameters.AddWithValue("@PhoneNumber", location.TelephoneNumber.ToString());
            else
                cmd.Parameters.AddWithValue("@PhoneNumber", DBNull.Value);

            cmd.Parameters.AddWithValue("@TempLocation", location.Temporary);
        }

        public LocationCheckin[] GetLatestCheckins()
        {
            List<LocationCheckin> posts = new List<LocationCheckin>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.GetFriendRecentLocations", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                posts.Add(DataRecordAdapter.CreateInstance<LocationCheckin>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return posts.ToArray();
        }

        /// <summary>
        /// Gets the latest posts that have been made to Gatsb.
        /// </summary>
        /// <param name="onlyWithAttachments">True when posts returned should
        /// only be ones with images and other attachments.</param>
        /// <param name="onlyFriends">Specifies if only friends posts should be retrieved.</param>
        /// <param name="count">Specifies the number of items to get.</param>
        /// <returns>The top latest posts.</returns>
        public Post[] GetLatestPosts(bool onlyWithAttachments, bool onlyFriends, int count)
        {
            List<Post> posts = new List<Post>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetLatestPosts", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@OnlyWithAttachments", onlyWithAttachments);
                        cmd.Parameters.AddWithValue("@OnlyFriends", onlyFriends);
                        cmd.Parameters.AddWithValue("@ItemCount", count);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                posts.Add(DataRecordAdapter.CreateInstance<Post>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return posts.ToArray();
        }

        public Post[] GetLatestUserPosts(Guid userId, int postCount)
        {
            List<Post> posts = new List<Post>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetLatestUserPosts", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@UserId", userId);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                posts.Add(DataRecordAdapter.CreateInstance<Post>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return posts.ToArray();
        }

        public Post[] GetLatestLocationPosts(Guid locationId, int postCount)
        {
            List<Post> posts = new List<Post>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetLatestLocationPosts", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@LocationId", locationId);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                posts.Add(DataRecordAdapter.CreateInstance<Post>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return posts.ToArray();
        }

        public Location GetLocation(Guid locationId)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetLocationById", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@LocationId", locationId);
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            return DataRecordAdapter.CreateInstance<Location>((IDataRecord)reader);
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public Location[] FindLocation(LocationSearchCriteria criteria)
        {
            List<Location> locations = new List<Location>();

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.FindLocationByName", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@Name", criteria.SearchString.ToLower().Replace("The ", "%"));
                        cmd.Parameters.AddWithValue("@PostalCode", criteria.PostalCode);
                        cmd.Parameters.AddWithValue("@LocationType", (int)criteria.Type);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                locations.Add(DataRecordAdapter.CreateInstance<Location>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return locations.ToArray();
        }

        public LocationCheckin GetUserCurrentLocation(Guid userId)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.GetUserCurrentLocation", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@UserId", userId);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();

                            if ((bool)reader["InLocation"])
                            {
                                Location loc = DataRecordAdapter.CreateInstance<Location>((IDataRecord)reader);
                                User usr = GatsbApplication.Current.GetUser(userId);

                                return new LocationCheckin((DateTime)reader["CheckinDate"], loc, usr); 
                            }
                            else
                            {
                                DateTime checkin = DateTime.MinValue;
                                if (!reader.IsDBNull(reader.GetOrdinal("CheckinDate")))
                                    checkin = (DateTime)reader["CheckinDate"];

                                User usr = GatsbApplication.Current.GetUser(userId);
                                return new LocationCheckin(checkin, null, usr);
                            }
                        }
                    }
                }
                catch(SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Gets a <see cref="Post"/> that has been made, by its identifier.
        /// </summary>
        /// <param name="id">The unique identifier of the post.</param>
        /// <returns>The post to get.</returns>
        public Post GetPost(Guid id)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetPostById", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@PostId", id);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            return DataRecordAdapter.CreateInstance<Post>((IDataRecord)reader);
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public LocationCheckin[] GetUserLocationHistory(Guid userId, int locationCount)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void AddValidationCode(Guid userId, UserValidationCode code)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.AddValidationCode", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@UserId", userId);
                        cmd.Parameters.AddWithValue("@Code", code.ToString());
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public bool ValidateCode(Guid userId, UserValidationCode code)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.ValidateCode", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@UserId", userId);
                        cmd.Parameters.AddWithValue("@Code", code.ToString());
                        return (bool)cmd.ExecuteScalar();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public void SetUserLocation(Guid userId, Guid locationId)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.SetUserLocation", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@UserId", userId);
                        if (locationId == Guid.Empty)
                            cmd.Parameters.AddWithValue("@LocationId", DBNull.Value);
                        else
                            cmd.Parameters.AddWithValue("@LocationId", locationId);

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        public User[] FindPeople(PersonSearchCriteria criteria)
        {
            if (criteria == null)
                throw new ArgumentNullException("criteria");
             
            List<User> users = new List<User>();

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.FindPeople", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        cmd.Parameters.AddWithValue("@SearchString", criteria.SearchString);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                users.Add(DataRecordAdapter.CreateInstance<User>((IDataRecord)reader));
                        }
                    }
                }
                catch(SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return users.ToArray();
        }

        /// <summary>
        /// Gets all friends of the specified user.
        /// </summary>
        /// <param name="userId">The user to get the friends of.</param>
        /// <returns>The array of users that are the friends of this user.</returns>
        public User[] GetUserFriends(Guid userId)
        {
            List<User> users = new List<User>();

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetUserFriends", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        cmd.Parameters.AddWithValue("@UserId", userId);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                users.Add(DataRecordAdapter.CreateInstance<User>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return users.ToArray();
        }

        public User[] GetLocationCurrentVisitors(Guid locationId)
        {
            List<User> users = new List<User>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetCurrentVisitorsOfLocation", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@LocationId", locationId);
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                users.Add(DataRecordAdapter.CreateInstance<User>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return users.ToArray();
        }

        public User[] GetLocationRecentVisitors(Guid locationId, DateTime sinceDate)
        {
            List<User> users = new List<User>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetRecentVisitorsOfLocation", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@LocationId", locationId);
                        cmd.Parameters.AddWithValue("@SinceDate", sinceDate);
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                users.Add(DataRecordAdapter.CreateInstance<User>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return users.ToArray();
        }

        /// <summary>
        /// Adds a user as a friend of the currently authenticated user.
        /// </summary>
        /// <param name="UserId">The unique identifier of the user to add as a friend.</param>
        public void AddFriend(Guid userId)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using(SqlCommand cmd = new SqlCommand("dbo.AddUserFriend", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        cmd.Parameters.AddWithValue("@FriendId", userId);

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Rejects a friendship request from a user, or ends a current friendship.
        /// </summary>
        /// <param name="userId">The user to reject the friendship request of</param>
        public void EndFriendship(Guid userId)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.EndUserFriendship", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        cmd.Parameters.AddWithValue("@FriendId", userId);

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Gets the currently authenticated users status information.
        /// </summary>
        /// <returns>The status information for the current user.</returns>
        public UserStatusInfo GetUserStatus()
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetUserStatus", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);

                        return new UserStatusInfo((int)cmd.ExecuteScalar());
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Gets messages in the inbox.
        /// </summary>
        /// <param name="status">The status of the messages to get.</param>
        /// <returns>The messages in the inbox.</returns>
        public InboxMessage[] GetMessages(MessageStatus status)
        {
            List<InboxMessage> messages = new List<InboxMessage>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetInboxMessages", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                                messages.Add(DataRecordAdapter.CreateInstance<InboxMessage>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return messages.ToArray();
        }

        /// <summary>
        /// Posts a <see cref="PostComment"/> to a post.
        /// </summary>
        /// <param name="comment">The comment to post.</param>
        public void PostComment(PostComment comment)
        {
            if (comment == null)
                throw new ArgumentNullException("comment");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.CreatePostComment", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);

                        cmd.Parameters.AddWithValue("@CommentId", comment.Id);
                        cmd.Parameters.AddWithValue("@PostId", comment.PostId);
                        cmd.Parameters.AddWithValue("@Body", comment.Body);

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Deletes a <see cref="PostComment"/>.
        /// </summary>
        /// <param name="commentId">The comment to delete.</param>
        public void DeleteComment(Guid commentId)
        {
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.DeletePostComment", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);

                        cmd.Parameters.AddWithValue("@CommentId", commentId);
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Gets the comments that have been placed on a post.
        /// </summary>
        /// <param name="postId">The identifier of the post to get the comments on.</param>
        /// <returns>An array of the comments made on a post.</returns>
        public PostComment[] GetPostComments(Guid postId)
        {
            List<PostComment> comments = new List<PostComment>();
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("dbo.GetPostComments", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd, true);
                        cmd.Parameters.AddWithValue("@PostId", postId);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while(reader.Read())
                                comments.Add(DataRecordAdapter.CreateInstance<PostComment>((IDataRecord)reader));
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return comments.ToArray();
        }

        /// <summary>
        /// Gets a workflow ID for an SMS session.
        /// </summary>
        /// <param name="recipientAddress">The address of the recipient, as the
        /// session token.</param>
        /// <returns>The unique identifier of the workflow.</returns>
        public Guid GetWorkflowForSmsSession(string recipientAddress)
        {
            if (recipientAddress == null)
                throw new ArgumentNullException("recipientAddress");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("GatsbWorkflows.dbo.GetWorkflowIdForCookie", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@Cookie", recipientAddress);

                        object obj = cmd.ExecuteScalar();

                        if (obj == DBNull.Value)
                            return Guid.Empty;
                        else
                            return (Guid)obj;
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Saves a workflow ID and gets an SMS session cookie.
        /// </summary>
        /// <param name="workflowId">The ID of the workflow to save.</param>
        /// <returns>The ID of the session (mailing address) for the session.</returns>
        public string SaveWorkflowId(Guid workflowId)
        {
            string sessionId = Guid.NewGuid().ToString();
            sessionId = sessionId.Substring(sessionId.Length - 10);
            string cookie = string.Format("wf_{0}@gatsb.com", sessionId);

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();
                    using (SqlCommand cmd = new SqlCommand("GatsbWorkflows.dbo.SaveSmsCookie", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        AddRequestingUserId(cmd);
                        cmd.Parameters.AddWithValue("@Cookie", cookie);
                        cmd.Parameters.AddWithValue("@WorkflowId", workflowId);

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return cookie;
        }

        #endregion

        private static void AddRequestingUserId(SqlCommand cmd, bool optional)
        {
            if (!(Thread.CurrentPrincipal is GatsbPrincipal) && optional)
                return;

            cmd.Parameters.AddWithValue("@RequestingUserId", GatsbPrincipal.Current.Identity.UserId);
        }

        private static void AddRequestingUserId(SqlCommand cmd)
        {
            AddRequestingUserId(cmd, false);
        }

        /// <summary>
        /// Gets locations that begin with a certain text and are near a specified zip code.
        /// </summary>
        /// <param name="nearZipCode">The zipcode to search near (optional)</param>
        /// <param name="page">The zero-based page to get.</param>
        /// <param name="pageSize">The size of the page.</param>
        /// <returns>The locations matching the specified criteria.</returns>
        public PaginationResult<Location> GetLocationsNear(string nearZipCode, int page, int pageSize)
        {
            if (pageSize < 1)
                throw new ArgumentOutOfRangeException("pageSize");

            if (page < 0)
                throw new ArgumentOutOfRangeException("pageSize");

            List<Location> locations = new List<Location>();
            int totalItems = 0;
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.GetLocations", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        AddRequestingUserId(cmd, true);

                        if (nearZipCode != null)
                            cmd.Parameters.AddWithValue("@PostalCode", nearZipCode);

                        cmd.Parameters.AddWithValue("@PageNumber", page);
                        cmd.Parameters.AddWithValue("@PageSize", pageSize);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                totalItems = (int)reader["LocationsTblTotalCount"];
                                locations.Add(DataRecordAdapter.CreateInstance<Location>((IDataRecord)reader));
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return new PaginationResult<Location>(locations, page, totalItems);
        }

        /// <summary>
        /// Gets locations that begin with a certain text and are near a specified zip code.
        /// </summary>
        /// <param name="userId">The ID of the user to get the locations for.</param>
        /// <param name="page">The zero-based page to get.</param>
        /// <param name="pageSize">The size of the page.</param>
        /// <returns>The locations matching the specified criteria.</returns>
        public PaginationResult<Location> GetUserLocations(Guid userId, int page, int pageSize)
        {
            if (pageSize < 1)
                throw new ArgumentOutOfRangeException("pageSize");

            if (page < 0)
                throw new ArgumentOutOfRangeException("pageSize");

            List<Location> locations = new List<Location>();
            int totalItems = 0;
            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using (SqlCommand cmd = new SqlCommand("dbo.GetUserLocationsPaginated", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        AddRequestingUserId(cmd, true);

                        if (userId != null)
                            cmd.Parameters.AddWithValue("@UserId", userId);

                        cmd.Parameters.AddWithValue("@PageNumber", page);
                        cmd.Parameters.AddWithValue("@PageSize", pageSize);

                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                totalItems = (int)reader["LocationsTblTotalCount"];
                                locations.Add(DataRecordAdapter.CreateInstance<Location>((IDataRecord)reader));
                            }
                        }
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return new PaginationResult<Location>(locations, page, totalItems);
        }

        public void SetupUpdatePhone(Guid userId, PhoneNumber newNumber, TelephoneServiceProvider provider)
        {
            if (provider == null)
                throw new ArgumentNullException("provider");

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            {
                try
                {
                    cxn.Open();

                    using(SqlCommand cmd = new SqlCommand("dbo.SetupUpdatePhone", cxn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@UserId", userId);
                        cmd.Parameters.AddWithValue("@MobileTel", newNumber.ToString());
                        cmd.Parameters.AddWithValue("@Provider", provider.GetTypeString());

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }
        }

        /// <summary>
        /// Gets or creates a geocoded location.
        /// </summary>
        /// <param name="name">The name of the location.</param>
        /// <param name="type">The type of the location.</param>
        /// <param name="position">The latitude longitude position.</param>
        /// <param name="city">The city the location is in.</param>
        /// <param name="state">The state the location is in.</param>
        /// <param name="zip">The zip code the location is in.</param>
        /// <param name="street">The optional street address.</param>
        /// <returns>The location that was created or retrieved.</returns>
        public Location GetGeocodedLocation(string name, LocationType type,
            Decav.Geography.LatLongPoint position, string city, string state, string zip, string street)
        {
            if (name == null)
                throw new ArgumentNullException("name");

            if (city == null)
                throw new ArgumentNullException("city");

            if (state == null)
                throw new ArgumentNullException("state");

            if (zip == null)
                throw new ArgumentNullException("zip");

            if (type != LocationType.CrossStreets && type != LocationType.StreetAddress)
                throw new ArgumentException("The only geocoded location types can be cross streets or addresses.");

            Guid locationId = Guid.Empty;

            using (SqlConnection cxn = SqlConnectionFactory.CreateConnection())
            using (SqlCommand cmd = new SqlCommand("dbo.GetGeocodedLocation", cxn))
            {
                try
                {
                    cxn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    AddRequestingUserId(cmd, true);
                    cmd.Parameters.AddWithValue("@Type", (int)type);
                    cmd.Parameters.AddWithValue("@Name", name);
                    cmd.Parameters.AddWithValue("@Coordinates", position.ToString());
                    cmd.Parameters.AddWithValue("@City", city);
                    cmd.Parameters.AddWithValue("@State", state);
                    cmd.Parameters.AddWithValue("@PostalCode", zip);

                    if (street != null)
                        cmd.Parameters.AddWithValue("@Street", street);
    
                    locationId = (Guid)cmd.ExecuteScalar();
                }
                catch (SqlException ex)
                {
                    SqlThrowHelper.ThrowException(ex);
                    throw;
                }
                finally
                {
                    cxn.Close();
                }
            }

            return GetLocation(locationId);
        }

        #region IDisposable Members

        public void Dispose()
        {
        }

        #endregion
    }
}       